package com.surelution.projects.crm
import java.util.Map;

import org.codehaus.groovy.grails.commons.ConfigurationHolder

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.grails.plugins.excelimport.ExcelImportUtils;
class OwnStationController {

    static allowedMethods = [save: "POST", update: "POST", delete: "POST"]
	def pictureService
		
	def exportService
	static Map CONFIG_OWN_STATION_MAP = [
		sheet:"a",
		startRow: 1,
		columnMap:  [
			'F':'stationCode',
			'G':'stationName',
			'H':'manager',
			'I':'address',
			'M':'ton',
			'N':'tonTitle',
			'Q':'star',
			'R':'starTitle',
			'O':'level',
			'P':'levelTitle',
			'AB':'generalArea',
			'AC':'buildingArea',
			'AD':'coverArea',
			'AE':'hallArea',
			'AF':'toiletArea',
			'AG':'parkingArea',
			'AI':'hasShop',
			'S':'managerTel'
		]
	]
	
    def index = {
        redirect(action: "list", params: params)
    }
	def reset = {
        def ownStationInstance = new OwnStation()
		ownStationInstance.latitude = params.lat
		ownStationInstance.longitude = params.lng
        ownStationInstance.name = params.name
        if (!ownStationInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
            redirect(action: "list")
        }
        else {
            return [ownStationInstance: ownStationInstance]
        }
	}
	
	def resetPosition = {
        def ownStationInstance = OwnStation.get(params.id)
        if (ownStationInstance) {
            if (params.version) {
                def version = params.version.toLong()
                if (ownStationInstance.version > version) {
                    ownStationInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'ownStation.label', default: 'OwnStation')] as Object[], "Another user has updated this OwnStation while you were editing")
                    render(view: "edit", model: [ownStationInstance: ownStationInstance])
                    return
                }
            }
            ownStationInstance.properties = params
            if (!ownStationInstance.hasErrors() && ownStationInstance.save(flush: true)) {
                flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), ownStationInstance.id])}"
                redirect(action: "show", id: ownStationInstance.id)
            }
            else {
                render(view: "edit", model: [ownStationInstance: ownStationInstance])
            }
        }
        else {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
            redirect(action: "list")
        }
    }
	
	def list = {
        def max = Math.min(params.max ? params.int('max') : 15, 100)
		
		def offset = params.int('offset')
		params.max = max
		params.offset = offset
		def cri = OwnStation.createCriteria().list([max:max, offset:offset]){
			eq('isActived', true)
		}
		[ownStationInstanceList: cri, ownStationInstanceTotal: cri.totalCount]
    }
	
	def editList = {
		params.max = Math.min(params.max ? params.int('max') : 10, 100)
		[ownStationInstanceList: OwnStation.list(params), ownStationInstanceTotal: OwnStation.count()]
	}
	
	def unLock = {
		def ownStationInstance = OwnStation.get(params.id)
		flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), ownStationInstance.id])}"
		if (ownStationInstance) {
			ownStationInstance.isLocked = false
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}
	
	def locked = {
		def ownStationInstance = OwnStation.get(params.id)
		flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), ownStationInstance.id])}"
		if (ownStationInstance) {
			ownStationInstance.isLocked = true
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}
	
	def unActived = {
		def ownStationInstance = OwnStation.get(params.id)
		flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), ownStationInstance.id])}"
		if (ownStationInstance) {
			ownStationInstance.isActived = false
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}
	def actived = {
		def ownStationInstance = OwnStation.get(params.id)
		flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), ownStationInstance.id])}"
		if (ownStationInstance) {
			ownStationInstance.isActived = true
			redirect(action: "editList")
		}
		else {
			redirect(action: "editList")
		}
	}

	def stationImport = {
	
	}

	def ImportExcelService
	def springSecurityService
	
	def sImport = {
		def file = params.stationFile
		def fileName = file.originalFilename
		Workbook workbook = WorkbookFactory.create(file.inputStream)
		List bookParamsList = ExcelImportUtils.columns(workbook, CONFIG_OWN_STATION_MAP)
		
		bookParamsList.each {row->
			println row
			OwnStation station = new OwnStation(name:"${row.stationName}", address:"${row.address}",shortName:"${row.stationName}",
				code:"${row.stationCode}", manager:"${row.manager}",isLocked:false,isActived:true,
				tonLevel:loadTonLevel("${row.ton}", "${row.tonTitle}"),star:loadStarLevel("${row.star}", "${row.starTitle}"), level:loadLevel("${row.level}", "${row.levelTitle}"),
				generalArea:"${row.generalArea}", buildingArea:"${row.buildingArea}", coverArea:"${row.coverArea}", hallArea:"${row.hallArea}", toiletArea:"${row.toiletArea}",
				parkingArea:"${row.parkingArea}",longitude:"0",latitude:"0", hasShop:"yes"=="${row.hasShop}", managerTel:"${row.managerTel}",
				)
			station.save(flush:true)
			println station
		}
		ImportExcel excel = ImportExcelService.storeFile(file.inputStream,fileName)
		excel.uploader = springSecurityService.principal.username
		excel.type = "中石油网点分布"
		excel.save()
		flash.message = "${message(code: 'default.updateFileSuccess.message', args: [message(code: 'ownStation.label', default: 'ownStation')])}"
		redirect(action: "list")
	}
	
//	help import OwnStation's level
	private Level loadLevel(String code, String name) {
		Level l = Level.findByCode(code)
		if(l == null) {
			l = new Level(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
	
	private StarLevel loadStarLevel(String code, String name) {
		StarLevel l = StarLevel.findByCode(code)
		if(l == null) {
			l = new StarLevel(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
	
	private TonLevel loadTonLevel(String code, String name) {
		TonLevel l = TonLevel.findByCode(code)
		if(l == null) {
			l = new TonLevel(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
	
	def export = {
		response.contentType = ConfigurationHolder.config.grails.mime.types['excel'] 
		response.setHeader("Content-disposition", "attachment; filename=station.xls")
		def result = OwnStation.list()
		def mapList = new ArrayList<HashMap>()
		result.each {
			def map = new HashMap()
			if (it.stationArea){
				map.put("stationArea", it.stationArea.areaName)
			}
			else map.put("stationArea", "")
			map.put("name", it.name)
			map.put("code", it.code)
			if (it.manageType){
				map.put("manageType", it.manageType.manageType)
			}
			else map.put("manageType", "")
			map.put("manager", it.manager)
			map.put("address", it.address)
			map.put("managerTel", it.managerTel)
			if (it.star){
				map.put("star", it.star.title)
			}
			else map.put("star", "")
			if (it.level){
				map.put("level", it.level.title)
			}
			else map.put("level", "")
			if (it.tonLevel){
				map.put("tonLevel", it.tonLevel.title)
			}
			else map.put("tonLevel", "")
			map.put("generalArea", it.generalArea)
			map.put("buildingArea", it.buildingArea)
			map.put("coverArea", it.coverArea)
			map.put("hallArea", it.hallArea)
			map.put("toiletArea", it.toiletArea)
			map.put("parkingArea", it.parkingArea)
			map.put("annualSale", it.annualSale)
			map.put("noGasAnnualSale", it.noGasAnnualSale)
			map.put("averageSale", it.averageSale)
			map.put("noGasAnnualSale", it.noGasAnnualSale)
			map.put("annualCardNums", it.annualCardNums)
			map.put("cardProp", it.cardProp)
			map.put("gasFuel", it.gasFuel)
			map.put("latitude", it.latitude)
			map.put("longitude", it.longitude)
			mapList.add(map)
		}
		List fields = ["stationArea","name","shortName","code","address","manager","managerTel","star","level","tonLevel","latitude","longitude","manageType","generalArea","buildingArea","coverArea","hallArea","toiletArea","parkingArea","annualSale","noGasAnnualSale","averageSale","noGasAnnualSale","annualCardNums","cardProp","gasFuel"]
		Map labels = ["stationArea":"所在片区","name":"站名","shortName":"简称","code":"编号","manageType":"管理类别","address":"地址","latitude":"经度","longitude":"纬度","manager":"站长","star":"星级","level":"等级","tonLevel":"吨级","managerTel":"站内电话","generalArea":"占地面积","buildingArea":"建筑面积","coverArea":"罩棚面积","hallArea":"营业厅面积","toiletArea":"公厕面积","parkingArea":"停车场面积","annualSale":"油品年度总销量(吨)","noGasAnnualSale":"非油年度总销量","averageSale":"油品日均销量(吨)","noGasAverageSale":"非油日均销量","annualCardNums":"发卡量","cardProp":"卡销比","gasFuel":"柴汽比"]

		exportService.export("excel", response.outputStream, mapList, fields, labels,[:],[:])     
		}	

    def create = {
		
        def ownStationInstance = new OwnStation()
		ownStationInstance.latitude = params.lat
		ownStationInstance.longitude = params.lng
        ownStationInstance.properties = params
        return [ownStationInstance: ownStationInstance]
    }

    def save = {
        def ownStationInstance = new OwnStation(params)
		def leaveInstance = Level.get(params.level.id)
		ownStationInstance.level = leaveInstance
		def starLeaveInstance = StarLevel.get(params.starLevel.id)
		ownStationInstance.star = starLeaveInstance
		def tonLeaveInstance = TonLevel.get(params.tonLevel.id)
		ownStationInstance.tonLevel = tonLeaveInstance
		ownStationInstance.isLocked = false
        if (ownStationInstance.save(flush: true)) {

            flash.message = "${message(code: 'default.created.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), ownStationInstance.id])}"
            redirect(action: "show", id: ownStationInstance.id)
        }
        else {
            render(view: "create", model: [ownStationInstance: ownStationInstance])
        }
    }

	def parseGasSale (OwnStation station) {
		Date now = new Date()
		def mapList = new ArrayList()
		(0.. 11).each{i->
			def firstSight = new Date(now.year,i,1)
			def endSight = new Date(now.year,i+1,1)
			String sql = '''select gs.station,sum(gs.saleNumber)
							from GasSale gs where gs.saleTime >= :start and gs.saleTime <= :end and gs.station = :station'''
			def result = GasSale.executeQuery(sql,["start":firstSight,"end":endSight,"station":station])
			if(result[0][1]){
				mapList.add(result[0][1]/1000/10)
			}
			else mapList.add("0")
		}
		def months = now.month
		def i = 1
		return mapList
	}
	
    def show = {
        def ownStationInstance = OwnStation.get(params.id)
        if (!ownStationInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
            redirect(action: "list")
        }
        else {
			Date now = new Date()
			def year = now.year+1900
			def num = parseGasSale(ownStationInstance)
			def numList = "0"
			num.each{m->
				numList += "," + m
			}
			def marketCount = Market.createCriteria().get{
				projections {
					count("id")
				}
				eq("station", ownStationInstance)
			}
			def marketSales = MarketGasSale.createCriteria().get{
				projections {
					sum("saleNumber")
					sum("dieselOilNumber")
				}
				createAlias('market','m')
				eq("sumType", MarketGasSale.SUM_TYPE_MONTH)
				eq("m.station", ownStationInstance)
			}
			ownStationInstance.metaClass.getMarketCount = {
				(marketCount)?marketCount:0
			}
			ownStationInstance.metaClass.getMarketGasSale = {
				(marketSales && marketSales[0])?marketSales[0]:0
			}
			ownStationInstance.metaClass.getMarketDieselSale = {
				(marketSales && marketSales[1])?marketSales[1]:0
			}
            [ownStationInstance: ownStationInstance,numList:numList,year:year]
        }
    }

    def edit = {
        def ownStationInstance = OwnStation.get(params.id)
        if (!ownStationInstance) {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
            redirect(action: "list")
        }
        else {
            return [ownStationInstance: ownStationInstance]
        }
    }
	
	def updatePic = {
		def ownStationInstance = OwnStation.get(params.id)
		println ownStationInstance
		if (ownStationInstance) {
			if (params.version) {
				def version = params.version.toLong()
				if (ownStationInstance.version > version) {
					ownStationInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'ownStation.label', default: 'OwnStation')] as Object[], "Another user has updated this OwnStation while you were editing")
					redirect(action: "show", id: ownStationInstance.id)
				}
			}
			else{
				flash.message = "${message(code: 'default.null.picture')}"
				redirect(action: "show", id: ownStationInstance.id)
			}
			ownStationInstance.properties = params
			if (!ownStationInstance.hasErrors()) {
				def file = params.OwnPicture
				def title = params.pictureTitle
				def description = params.description
				if(title == "" && !file.isEmpty()) {
					flash.message = "${message(code: 'default.not.found.title')}"
					redirect(action: "show", id: ownStationInstance.id)
				}
				if(title != "" && !file.isEmpty()) {
					Picture picture = pictureService.storePicture(file.inputStream)
					picture.title = title
					picture.description = description
					picture.save(flush:true)
					
					StationPicture stationPicture = new StationPicture()
					stationPicture.station = ownStationInstance
					stationPicture.picture = picture
					stationPicture.isDefault = false
					try{
						ownStationInstance.addToPictures(stationPicture)
						stationPicture.save(flush:true)
					}catch(e) {
						e.printStackTrace()
					}
					flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), ownStationInstance.id])}"
					redirect(action: "show", id: ownStationInstance.id)
				}
			}
			else {
				flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'ownStation'), params.id])}"
				redirect(action: "list")
			}
		}
	}

    def update = {
        def ownStationInstance = OwnStation.get(params.id)
        if (ownStationInstance) {
            if (params.version) {
                def version = params.version.toLong()
                if (ownStationInstance.version > version) {
                    ownStationInstance.errors.rejectValue("version", "default.optimistic.locking.failure", [message(code: 'ownStation.label', default: 'OwnStation')] as Object[], "Another user has updated this OwnStation while you were editing")
                    render(view: "edit", model: [ownStationInstance: ownStationInstance])
                    return
                }
            }
            ownStationInstance.properties = params
            if (!ownStationInstance.hasErrors() && ownStationInstance.save(flush: true)) {
                flash.message = "${message(code: 'default.updated.message', args: [message(code: 'ownStation.label', default: 'ownStation'), params.id])}"
                redirect(action: "show", id: ownStationInstance.id)
            }
	        else {
	            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'ownStation'), params.id])}"
	            redirect(action: "list")
	        }
			redirect(action: "show", id: ownStationInstance.id)
        }
    }

    def delete = {
        def ownStationInstance = OwnStation.get(params.id)
        if (ownStationInstance) {
            try {
                ownStationInstance.delete(flush: true)
                flash.message = "${message(code: 'default.deleted.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
                redirect(action: "list")
            }
            catch (org.springframework.dao.DataIntegrityViolationException e) {
                flash.message = "${message(code: 'default.not.deleted.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
                redirect(action: "show", id: params.id)
            }
        }
        else {
            flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'ownStation.label', default: 'OwnStation'), params.id])}"
            redirect(action: "list")
        }
    }
}
